<%@ page import="net.cyue.homework.student.management.util.DB" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="net.cyue.homework.student.management.entity.Student" %>
<%@ page import="java.util.*" %>
<%@ page import="net.cyue.homework.student.management.entity.Major" %>
<%@ page import="net.cyue.homework.student.management.entity.Job" %>
<%@ page import="net.cyue.homework.student.management.entity.MajorClass" %>
<%@ page contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<%!
    String getMajor(int majorID) throws SQLException {
        ResultSet resultSet = DB.POOL.query("select name from major where id =" + majorID);
        if (resultSet.next()) {
            String name = resultSet.getString("name");
            resultSet.close();
            return name;
        }
        return null;
    }
    String getClassName(int classID) throws SQLException {
        ResultSet resultSet = DB.POOL.query("select name from major_class where id =" + classID);
        if (resultSet.next()) {
            String name = resultSet.getString("name");
            resultSet.close();
            return name;
        }
        return null;
    }
    String getJob(int jobID) throws SQLException {
        ResultSet resultSet = DB.POOL.query("select name from job where id =" + jobID);
        if (resultSet.next()) {
            String name = resultSet.getString("name");
            resultSet.close();
            return name;
        }
        return null;
    }
%>
<%
    // 查询参数
    String studentID = request.getParameter("id");
    String studentName = request.getParameter("name");
    String studentGender = request.getParameter("gender");
    String studentMajor = request.getParameter("major");
    String studentMajorClass = request.getParameter("class");
    String studentJob = request.getParameter("job");
    String studentBirthdayStart = request.getParameter("birthdayStart");
    String studentBirthdayEnd = request.getParameter("birthdayEnd");

    StringBuilder conditionBuilder = new StringBuilder();
    if (studentID != null && !studentID.isEmpty()) {
        conditionBuilder.append(" and student_id = ").append(studentID);
    }
    if (studentName != null && !studentName.isEmpty()) {
        conditionBuilder.append(" and name like '%").append(studentName).append("%'");
    }
    if (studentGender != null && !studentGender.isEmpty()) {
        conditionBuilder.append(" and gender = '").append(studentGender).append("'");
    }
    if (studentMajor != null && !studentMajor.isEmpty()) {
        conditionBuilder.append(" and major_id = ").append(studentMajor);
    }
    if (studentMajorClass != null && !studentMajorClass.isEmpty()) {
        conditionBuilder.append(" and class_id = ").append(studentMajorClass);
    }
    if (studentJob != null && !studentJob.isEmpty()) {
        conditionBuilder.append(" and job_id = ").append(studentJob);
    }
    if (studentBirthdayStart != null && !studentBirthdayStart.isEmpty()) {
        conditionBuilder.append(" and birthday >= '").append(studentBirthdayStart).append("'");
    }
    if (studentBirthdayEnd != null && !studentBirthdayEnd.isEmpty()) {
        conditionBuilder.append(" and birthday <= '").append(studentBirthdayEnd).append("'");
    }
    String sqlCondition = conditionBuilder.toString();

    // 分页查询学生信息
    int pageSize = 5;
    int selectPage = 1;
    String selectPageString =  request.getParameter("page");
    if (selectPageString != null && !selectPageString.isEmpty()) {
        selectPage = Integer.parseInt(selectPageString);
    }
    // 查询总数
    int studentCount = 0;
    int conditionalStudentCount = 0;
    ResultSet countResultSet = DB.POOL.query("select count(1) from student");
    countResultSet.next();
    studentCount = countResultSet.getInt(1);
    countResultSet.close();

    // 查询条件下总数
    ResultSet countResultSet2 = DB.POOL.query("select count(1) from student where 1 = 1" + sqlCondition);
    if (countResultSet2.next()) {
        conditionalStudentCount = countResultSet2.getInt(1);
    }
    countResultSet2.close();
    int pageCount = (int) Math.ceil((double) conditionalStudentCount / pageSize);

    // 超出范围
    if (selectPage < 1 || selectPage > pageCount) {
        response.sendRedirect("./admin.jsp");
    }

    // 获取数据
    List<Major> majorList = new ArrayList<>();
    List<MajorClass> majorClassList = new ArrayList<>();
    List<Job> jobList = new ArrayList<>();
    List<Student> studentList = new ArrayList<>();

    // 查询专业信息
    ResultSet majorResultSet = DB.POOL.query("select * from major");
    while (majorResultSet.next()) {
        int id = majorResultSet.getInt("id");
        String majorName = majorResultSet.getString("name");
        majorList.add(new Major(id, majorName));
    }
    majorResultSet.close();

    // 查询班级信息
    StringBuilder classSQLRangeBuilder = new StringBuilder("(");
    for (int i = 0; i < majorList.size(); i++) {
        classSQLRangeBuilder.append(majorList.get(i).getId());
        if (i != majorList.size() - 1) {
            classSQLRangeBuilder.append(",");
        }
    }
    classSQLRangeBuilder.append(")");
    ResultSet majorClassResultSet = DB.POOL.query("select * from major_class where major_id in " + classSQLRangeBuilder);
    while (majorClassResultSet.next()) {
        int id = majorClassResultSet.getInt("id");
        int majorID = majorClassResultSet.getInt("major_id");
        String majorClassName = majorClassResultSet.getString("name");
        majorClassList.add(new MajorClass(id, majorID, majorClassName));
    }
    majorClassResultSet.close();

    // 查询职务信息
    ResultSet jobResultSet = DB.POOL.query("select * from job");
    while (jobResultSet.next()) {
        int id = jobResultSet.getInt("id");
        String jobName = jobResultSet.getString("name");
        jobList.add(new Job(id, jobName));
    }
    jobResultSet.close();

    int pageStartOffset = (selectPage - 1) * pageSize;
    String sql = "select * from student where 1 = 1" + sqlCondition + " limit " + pageStartOffset + ", " + pageSize;
    ResultSet resultSet = DB.POOL.query(sql);
    while (resultSet.next()) {
        int id = resultSet.getInt("student_id");
        String name = resultSet.getString("name");
        String gender = resultSet.getString("gender");
        int majorID = resultSet.getInt("major_id");
        int majorClassID = resultSet.getInt("class_id");
        int jobID = resultSet.getInt("job_id");
        String phone = resultSet.getString("phone");
        Date birthday = resultSet.getDate("birthday");
        Student student = new Student(id, name, gender, majorID, majorClassID, jobID, phone, birthday);
        studentList.add(student);
    }
    resultSet.close();
%>
<html>
<head>
    <meta charset="UTF-8">
    <title>洪炜斐——学生管理系统——学生管理</title>
    <link rel="stylesheet" href="./css/common.css">
    <link rel="stylesheet" href="./css/style.css">
    <link rel="stylesheet" href="./css/hf.css">
    <link rel="stylesheet" href="./css/table.css">
    <link rel="stylesheet" href="./css/custom.css">

    <style>
        main {
            padding-bottom: 5em;
        }
    </style>
</head>
<body>
    <!-- 添加学生信息 dialog -->
    <dialog id="dialog-student-add">
        <div class="text-center">
            <h3 style="margin: 0 0 1em;">添加学生</h3>
        </div>
        <form id="form-student-add" class="form gap-1em">
            <div class="form-item">
                <label>
                    学号
                </label>
                <input type="text" name="studentId" placeholder="请输入学号" required>
                <span></span>
            </div>
            <div class="form-item">
                <label>
                    姓名
                </label>
                <input type="text" name="name" placeholder="请输入姓名" required>
                <span></span>
            </div>
            <div class="form-item">
                <label>
                    性别
                </label>
                <label style="width: fit-content;">
                    <input class="no-border no-shadow" type="radio" name="gender" value="男" checked>
                    男
                </label>
                <label style="width: fit-content;">
                    <input class="no-border no-shadow" type="radio" name="gender" value="女">
                    女
                </label>
            </div>
            <div>
                <label>
                    专业
                </label>
                <select name="majorId" required>
                    <option value="">请选择专业</option>
                    <%
                        for (Major major : majorList) {
                    %>
                        <option value="<%= major.getId() %>"><%= major.getName() %></option>
                    <%
                        }
                    %>
                </select>
                <span></span>
            </div>
            <div>
                <label>
                    班级
                </label>
                <select name="classId" required>
                    <option value="">请选择班级</option>
                    <%
                        for (MajorClass majorClass : majorClassList) {
                    %>
                        <option value="<%= majorClass.getId() %>"><%= majorClass.getName() %></option>
                    <%
                        }
                    %>
                </select>
            </div>
            <div>
                <label>
                    职务
                </label>
                <select name="jobId">
                    <option value="">请选择职务</option>
                    <%
                        for (Job job : jobList) {
                    %>
                        <option value="<%= job.getId() %>"><%= job.getName() %></option>
                    <%
                        }
                    %>
                </select>
            </div>
            <div>
                <label>
                    电话
                </label>
                <input type="text" name="phone" placeholder="请输入电话">
            </div>
            <div>
                <label>
                    生日
                </label>
                <input type="date" name="birthday">
            </div>
            <div class="flex-row justify-center gap-1em">
                <input class="button primary" type="submit" value="确定">
                <input class="button" type="reset" value="取消" onclick="closeAddStudent()">
            </div>
        </form>
    </dialog>

    <!-- 修改学生信息 dialog -->
    <dialog id="dialog-student-update">
        <div class="text-center">
            <h3 style="margin: 0 0 1em;">修改学生信息</h3>
        </div>
        <form id="form-student-update" class="form gap-1em">
            <div class="form-item">
                <label>
                    姓名
                </label>
                <input type="text" name="name" placeholder="请输入姓名" required>
                <span></span>
            </div>
            <div class="form-item">
                <label>
                    性别
                </label>
                <label style="width: fit-content;">
                    <input class="no-border no-shadow" type="radio" name="gender" value="男" checked>
                    男
                </label>
                <label style="width: fit-content;">
                    <input class="no-border no-shadow" type="radio" name="gender" value="女">
                    女
                </label>
            </div>
            <div>
                <label>
                    专业
                </label>
                <select name="majorId" required>
                    <option value="">请选择专业</option>
                    <%
                        for (Major major : majorList) {
                    %>
                    <option value="<%= major.getId() %>"><%= major.getName() %></option>
                    <%
                        }
                    %>
                </select>
                <span></span>
            </div>
            <div>
                <label>
                    班级
                </label>
                <select name="classId" required>
                    <option value="">请选择班级</option>
                    <%
                        for (MajorClass majorClass : majorClassList) {
                    %>
                    <option value="<%= majorClass.getId() %>"><%= majorClass.getName() %></option>
                    <%
                        }
                    %>
                </select>
            </div>
            <div>
                <label>
                    职务
                </label>
                <select name="jobId">
                    <option value="">请选择职务</option>
                    <%
                        for (Job job : jobList) {
                    %>
                    <option value="<%= job.getId() %>"><%= job.getName() %></option>
                    <%
                        }
                    %>
                </select>
            </div>
            <div>
                <label>
                    电话
                </label>
                <input type="text" name="phone" placeholder="请输入电话">
            </div>
            <div>
                <label>
                    生日
                </label>
                <input type="date" name="birthday">
            </div>
            <div class="flex-row justify-center gap-1em">
                <input class="button primary" type="submit" value="确定">
                <input class="button" type="reset" value="取消" onclick="closeUpdateStudent()">
            </div>
        </form>
    </dialog>

    <jsp:include page="component/header.jsp">
    	<jsp:param name="page" value="学生管理"/>
    </jsp:include>
    <main>
        <div class="panel items-center">
            <h2>学生信息管理</h2>
            <div class="flex-column gap-1em items-center">
                <!-- 增加 -->
                <div class="flex-row" style="width: 75%">
                    <button class="button primary" onclick="openAddStudent()">添加学生</button>
                </div>
                <!-- 查询 -->
                <div style="width: 75%">
                    <form class="flex-row flex-wrap gap-1em" method="get" action="admin.jsp">
                        <div class="form-item">
                            <label for="input-name">
                                姓名
                            </label>
                            <input id="input-name" type="text" name="name">
                        </div>
                        <div>
                            <label for="input-gender">
                                性别
                            </label>
                            <input id="input-gender" type="text" name="gender">
                        </div>
                        <div class="form-item">
                            <label for="select-major">
                                专业
                            </label>
                            <select id="select-major" name="major">
                                <option value="">选择要查询的专业</option>
                                <%
                                    for (Major m : majorList) {
                                %>
                                    <option value="<%= m.getId() %>"><%= m.getName() %></option>
                                <%}%>
                            </select>
                        </div>
                        <div class="form-item">
                            <label for="select-class">
                                班级
                            </label>
                            <select id="select-class" name="class">
                                <option value="">选择要查询的班级</option>
                                <%
                                    for (MajorClass c : majorClassList) {
                                %>
                                <option value="<%= c.getId() %>"><%= c.getName() %></option>
                                <%}%>
                            </select>
                        </div>
                        <div class="form-item">
                            <label for="select-job">
                                职务
                            </label>
                            <select id="select-job" name="job">
                                <option value="">选择要查询的职务</option>
                                <%
                                    for (Job j : jobList) {
                                %>
                                    <option value="<%= j.getId() %>"><%= j.getName() %></option>
                                <%}%>
                            </select>
                        </div>
                        <div class="form-item">
                            <label for="input-birthday">
                                生日
                            </label>
                            <input id="input-birthday" type="date" name="birthdayStart">
                            <span>——</span>
                            <input type="date" name="birthdayEnd">
                        </div>
                        <div>
                            <input class="button primary" type="submit" value="查询">
                        </div>
                    </form>
                </div>
                <!-- 数据展示 -->
                <div>
                    <table>
                        <thead>
                        <tr>
                            <th>学号</th>
                            <th>姓名</th>
                            <th>性别</th>
                            <th>专业</th>
                            <th>班级</th>
                            <th>职务</th>
                            <th>手机号</th>
                            <th>出生日期</th>
                            <th>操作</th>
                        </tr>
                        </thead>
                        <tbody>
                        <%
                            for(Student student : studentList) {
                        %>
                            <tr>
                                <td><%= student.getStudentId() %></td>
                                <td><%= student.getName() %></td>
                                <td><%= student.getGender() %></td>
                                <td><%= getMajor(student.getMajorId()) %></td>
                                <td><%= getClassName(student.getClassId()) %></td>
                                <td><%= getJob(student.getJobId()) %></td>
                                <td><%= student.getPhone() %></td>
                                <td><%= student.getBirthday() %></td>
                                <td>
                                    <button
                                        class="button primary"
                                        onclick="openUpdateStudent(<%= student.getStudentId() %>)"
                                    >
                                        修改
                                    </button>
                                    <button
                                        class="button danger"
                                        onclick="deleteStudent(<%= student.getStudentId() %>)"
                                    >
                                        删除
                                    </button>
                                </td>
                            </tr>
                        <% } %>
                        </tbody>
                    </table>
                    <div>
                        <p>
                            总共有<span><%= studentCount %></span>条数据
                        </p>
                        <p>
                            条件下共有<span><%= conditionalStudentCount %></span>条数据
                        </p>
                        <p>
                            条件下共有<span><%= pageCount %></span>页
                        </p>
                    </div>
                    <!-- 切换显示页数 -->
                    <div>
                        <ul class="flex-row justify-center">
                            <li>
                                <a href="javascript: changePage(1)">首页</a>
                            </li>
                            <li>
                                <a href="javascript: changePage(<%= selectPage %> - 1)">上一页</a>
                            </li>
                            <li>
                                <a href="javascript: changePage(<%= selectPage %> + 1)">下一页</a>
                            </li>
                            <li>
                                <a href="javascript: changePage(<%= pageCount %>)">尾页</a>
                            </li>
                            <li>
                                第<span><%= selectPage %></span>/<span><%= pageCount %></span>页
                            </li>
                        </ul>
                    </div>

                </div>
            </div>
        </div>
    </main>

    <script>
        function changePage(page) {
            if (page < 1 || page > <%= pageCount %>) {
                alert("页数超出范围");
                return;
            }
            const query = window.location.search.substring(1);
            const params = query.split("&");
            const map = new Map();
            for (let i = 0; i < params.length; i++) {
                const param = params[i].split("=");
                map.set(param[0], param[1]);
            }
            map.set("page", page);
            // [key, value]
            // [["page", "1"], ["major", "1"]]
            // [“page”, "1"].join("=") => "page=1"
            // ["page=1", "major=1"]
            // "page=1&major=1"
            window.location.href = window.location.pathname + "?" +
                Array.from(map.entries()).map(param => param.join("=")).join("&");
        }

        let wannaUpdateStudentID = undefined;
        const addStudentDialog = document.querySelector("#dialog-student-add");
        const updateStudentDialog = document.querySelector("#dialog-student-update");
        const addStudentForm = document.querySelector("#form-student-add");
        const updateStudentForm = document.querySelector("#form-student-update");
        function openAddStudent() {
            addStudentDialog.showModal();
        }
        function closeAddStudent() {
            addStudentForm.reset();
            addStudentDialog.close();
        }
        function openUpdateStudent(id) {
            wannaUpdateStudentID = id;
            fetch("./api/student?id=" + id)
            .then(res => res.json())
            .then(result => {
                const student = result.data;
                const birthdayDate = new Date(student.birthday);
                let month = (birthdayDate.getMonth() + 1).toString();
                if (month.length === 1) {
                    month = "0" + month;
                }
                student.birthday = birthdayDate.getFullYear() + "-" + month + "-" + (birthdayDate.getDate());
                updateStudentForm.name.value = student.name;
                updateStudentForm.gender.value = student.gender;
                updateStudentForm.majorId.value = student.majorId;
                updateStudentForm.classId.value = student.classId;
                updateStudentForm.jobId.value = student.jobId;
                updateStudentForm.phone.value = student.phone;
                updateStudentForm.birthday.value = student.birthday;
            });
            updateStudentDialog.showModal();
        }
        function closeUpdateStudent() {
            updateStudentForm.reset();
            updateStudentDialog.close();
        }


        addStudentForm.onsubmit = (e) => {
            e.preventDefault();

            fetch("./api/student", {
                method: "POST",
                headers: {
                    "Content-Type": "application/json"
                },
                body: JSON.stringify({
                    studentId: parseInt(addStudentForm.studentId.value),
                    name: addStudentForm.name.value,
                    gender: addStudentForm.gender.value,
                    majorId: parseInt(addStudentForm.majorId.value),
                    classId: parseInt(addStudentForm.classId.value),
                    jobId: parseInt(addStudentForm.jobId.value),
                    phone: parseInt(addStudentForm.phone.value),
                    birthday: new Date(addStudentForm.birthday.value).toDateString()
                }),
            })
            .then(res => res.json())
            .then(result => {
                if (result.code !== 200) {
                    alert("添加失败，也许是专业与班级不对应");
                    return;
                }
                alert("添加成功");
                window.location.reload();
            });
        }
        updateStudentForm.onsubmit = (e) => {
            e.preventDefault();

            fetch("./api/student", {
                method: "PUT",
                headers: {
                    "Content-Type": "application/json"
                },
                body: JSON.stringify({
                    studentId: wannaUpdateStudentID,
                    name: updateStudentForm.name.value,
                    gender: updateStudentForm.gender.value,
                    majorId: parseInt(updateStudentForm.majorId.value),
                    classId: parseInt(updateStudentForm.classId.value),
                    jobId: parseInt(updateStudentForm.jobId.value),
                    phone: updateStudentForm.phone.value,
                    birthday: new Date(updateStudentForm.birthday.value).toDateString()
                }),
            })
            .then(res => res.json())
            .then(result => {
                if (result.code !== 200) {
                    alert("更新失败，也许是专业与班级不对应");
                    return;
                }
                alert("更新成功");
                window.location.reload();
            });
        }

        function deleteStudent(id) {
            if (!window.confirm("是否删除")) {
                return;
            }
            console.log("删除学生" + id);
            fetch("./api/student", {
                method: "DELETE",
                headers: {
                    "Content-Type": "application/json"
                },
                body: JSON.stringify({
                    id: id
                })
            })
            .then(res => res.json())
            .then(result => {
                if (result.code !== 200) {
                    alert("删除失败");
                    return;
                }
                alert("删除成功");
                window.location.reload();
            });
        }
    </script>
</body>
</html>


